package com.zhan.ielts.tools;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import net.htmlparser.jericho.Config;
import net.htmlparser.jericho.Element;
import net.htmlparser.jericho.LoggerProvider;
import net.htmlparser.jericho.Source;
import net.htmlparser.jericho.StartTag;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zhan.ielts.domain.WordPreRootSufix;

public class IcibaFile {
	private static Logger log = LoggerFactory.getLogger(IcibaFile.class);

	public static void main(String[] args) throws ClassNotFoundException, SQLException {

		System.out.println("---start---");

//		String fileName = "D:/xiaozhan/received files/xiaoxiao/static.html";
//
//		// parserHtml(fileName);
//		// parserHtmlSecontType(fileName);
//
//		String dir = "/usr/local/backup/html-4";
//		File folder = new File(dir);
//		File[] files = folder.listFiles();
//		log.info("" + files.length);
//		for (int i = 0; i < files.length; i++) {
////			 log.info(""+files[i].getAbsoluteFile());
////			parserHtmlSecontType(files[i].getAbsolutePath());
//		}
//
//		//导出excel
//		String excelFilePath = "/home/neo/文档/QQrecords/cigen.xlsx";
//		List<wordpreroot> list = DictionaryUtils.getWordPreRootSufix(0, Integer.MAX_VALUE);
//		exportWordPreRootSufix(excelFilePath, list);
		
		//导入修改后的词根词缀
//		String rootPath = "D:/xiaozhan/received files/jiang qian/0330/词根词缀——0405.xlsx";
//		String rootPath = "D:/xiaozhan/received files/jiang qian/0330/词根词性相同的单词.xlsx";
		String rootPath = "/home/neo/文档/QQrecords/cigencizhui_0801.xlsx";
//		importEtymaAffix(rootPath);
		fixWordPreRootSufix();
		
		System.out.println("---end---");
	}

	@SuppressWarnings("unused")
	public static JSONObject parserHtml(String fileName) {
		JSONObject result = new JSONObject();
		result.put("state", "ok");
		File file = new File(fileName);
		if (!file.exists() || !file.isFile() || file.length() <= 0) {
			result.put("message", "文件错误");
			result.put("state", "error");
			return result;
		}
		Source source = null;
		try {
			Config.LoggerProvider = LoggerProvider.DISABLED;
			source = new Source(file);
			if (source == null) {
				result.put("message", "读取文件失败");
				result.put("state", "error");
				return result;
			}
		} catch (IOException e) {
			e.printStackTrace();
			result.put("message", "读取文件失败");
			result.put("state", "error");
			return result;
		}

		List<StartTag> tagList = source.getAllStartTagsByClass("error-note");
		if (null != tagList && tagList.size() > 0) {
			Element el = tagList.get(0).getElement().getChildElements().get(0).getChildElements().get(0);
			String message = el.getContent().toString();
			result.put("message", filterSpecialChar(message));
			result.put("state", "error");
			return result;
		}
		// content.put("EE", _transEE(source.getElementById("article")));
		// content.put("EE", _transEE(source.getElementById("article")));
		List<Element> infoArticleElementList = source.getAllElementsByClass("info-article");
		// List<Element> elementList =
		// source.getAllElementsByClass("word-root");
		// Element element = elementList.get(0).getParentElement();

		JSONObject jsonObject = new JSONObject();
		JSONObject wordPrefixJsonObject = new JSONObject();
		JSONObject wordRootJsonObject = new JSONObject();
		JSONObject wordSuffixJsonObject = new JSONObject();

		for (int i = 0; i < infoArticleElementList.size(); i++) {
			List<Element> tArticleList = infoArticleElementList.get(i).getAllElementsByClass("article-list");
			if (tArticleList != null && tArticleList.size() == 1) {
				List<Element> tCurrentList = tArticleList.get(0).getAllElementsByClass("current");
				if (tCurrentList != null && tCurrentList.size() == 1) {

					String wordRootText = tCurrentList.get(0).getContent().toString();
					if (wordRootText != null && wordRootText.equals("词根词缀")) {
						System.out.println("zhao-dao-le");

						List<Element> articleList = infoArticleElementList.get(i).getAllElementsByClass("article");
						if (articleList != null && articleList.size() == 1) {
							Element articleElement = articleList.get(0);
							List<Element> elementList = articleElement.getChildElements();
							String wordPrefix = "";
							String wordRoot = "";
							String wordSuffix = "";
							String flag = "1";
							JSONArray wordPrefixJSONArray = new JSONArray();
							JSONArray wordRootJSONArray = new JSONArray();
							JSONArray wordSuffixJSONArray = new JSONArray();
							// 找到词根词缀，开始解析
							for (int j = 0; j < elementList.size(); j++) {
								Element element = elementList.get(j);
								element.getContent().toString();
								String className = element.getAttributeValue("class");

								// 词根词缀名处理
								if (className.contains("word-root")) {
									//
									String tmpStr = element.getContent().toString();
									if (tmpStr.startsWith("前缀")) { // 前缀
										wordPrefix = tmpStr;
										flag = "1";
										wordPrefixJsonObject.put("word", wordPrefix);
									} else if (tmpStr.startsWith("词根")) { // 词根
										wordRoot = tmpStr;
										flag = "2";
										wordRootJsonObject.put("word", wordRoot);
									} else if (tmpStr.startsWith("后缀")) { // 后缀
										wordSuffix = tmpStr;
										flag = "3";
										wordSuffixJsonObject.put("word", wordSuffix);
									}

								} else if (className.contains("collins-section")) { // 词根词缀内容处理
									if (flag.equals("1")) {
										JSONObject wordClassJsonObject = new JSONObject();

										List<Element> collinsSectionList = element.getChildElements();
										if (collinsSectionList != null && collinsSectionList.size() > 0) {
											JSONArray wordDetailJSONArray = new JSONArray();
											for (int k = 0; k < collinsSectionList.size(); k++) {
												Element subElement = collinsSectionList.get(k);
												String subClassName = subElement.getAttributeValue("class");

												if (subClassName.contains("section-h")) { // section-h
													List<Element> sectionHList = subElement
															.getAllElementsByClass("family-english");
													String hOrder = sectionHList.get(0).getContent().toString();
													String wordClass = sectionHList.get(1).getContent().toString();
													wordClassJsonObject.put("wordClass", hOrder + ":" + wordClass);
												} else if (subClassName.contains("section-prep")) { // section-prep
													// prep-order-icon
													String prepOrderIcon = subElement
															.getAllElementsByClass("prep-order-icon").get(0)
															.getContent().toString();
													// prep-order
													Element prepOrderElement = subElement.getAllElementsByClass(
															"prep-order").get(0);
													List<Element> subPreOrderList = prepOrderElement.getChildElements();
													String word = subPreOrderList.get(0).getTextExtractor().toString();
													String explain = subPreOrderList.get(1).getTextExtractor()
															.toString();

													wordDetailJSONArray
															.add(prepOrderIcon + " " + word + "\n" + explain);

												}
											}
											wordClassJsonObject.put("detail", wordDetailJSONArray);
										}

										wordPrefixJSONArray.add(wordClassJsonObject);
									} else if (flag.equals("2")) {
										JSONObject wordClassJsonObject = new JSONObject();

										List<Element> collinsSectionList = element.getChildElements();
										if (collinsSectionList != null && collinsSectionList.size() > 0) {
											JSONArray wordDetailJSONArray = new JSONArray();
											for (int k = 0; k < collinsSectionList.size(); k++) {
												Element subElement = collinsSectionList.get(k);
												String subClassName = subElement.getAttributeValue("class");

												if (subClassName.contains("section-h")) { // section-h
													List<Element> sectionHList = subElement
															.getAllElementsByClass("family-english");
													String hOrder = sectionHList.get(0).getContent().toString();
													String wordClass = sectionHList.get(1).getContent().toString();
													wordClassJsonObject.put("wordClass", hOrder + ":" + wordClass);
												} else if (subClassName.contains("section-prep")) { // section-prep
													// prep-order-icon
													String prepOrderIcon = subElement
															.getAllElementsByClass("prep-order-icon").get(0)
															.getContent().toString();
													// prep-order
													Element prepOrderElement = subElement.getAllElementsByClass(
															"prep-order").get(0);
													List<Element> subPreOrderList = prepOrderElement.getChildElements();
													String word = subPreOrderList.get(0).getTextExtractor().toString();
													String explain = subPreOrderList.get(1).getTextExtractor()
															.toString();

													wordDetailJSONArray
															.add(prepOrderIcon + " " + word + "\n" + explain);

												}
											}
											wordClassJsonObject.put("detail", wordDetailJSONArray);
										}

										wordRootJSONArray.add(wordClassJsonObject);
									} else if (flag.equals("3")) { // 后缀
										JSONObject wordClassJsonObject = new JSONObject();

										List<Element> collinsSectionList = element.getChildElements();
										if (collinsSectionList != null && collinsSectionList.size() > 0) {
											JSONArray wordDetailJSONArray = new JSONArray();
											for (int k = 0; k < collinsSectionList.size(); k++) {
												Element subElement = collinsSectionList.get(k);
												String subClassName = subElement.getAttributeValue("class");

												if (subClassName.contains("section-h")) { // section-h
													List<Element> sectionHList = subElement
															.getAllElementsByClass("family-english");
													String hOrder = sectionHList.get(0).getContent().toString();
													String wordClass = sectionHList.get(1).getContent().toString();
													wordClassJsonObject.put("wordClass", hOrder + ":" + wordClass);
												} else if (subClassName.contains("section-prep")) { // section-prep
													// prep-order-icon
													String prepOrderIcon = subElement
															.getAllElementsByClass("prep-order-icon").get(0)
															.getContent().toString();
													// prep-order
													Element prepOrderElement = subElement.getAllElementsByClass(
															"prep-order").get(0);
													List<Element> subPreOrderList = prepOrderElement.getChildElements();
													String word = subPreOrderList.get(0).getTextExtractor().toString();
													String explain = subPreOrderList.get(1).getTextExtractor()
															.toString();

													wordDetailJSONArray
															.add(prepOrderIcon + " " + word + "\n" + explain);

												}
											}
											wordClassJsonObject.put("detail", wordDetailJSONArray);
										}

										wordSuffixJSONArray.add(wordClassJsonObject);
									} else {
										// 不是有效数据
										System.err.println("---err");
									}

								}

							}

							wordPrefixJsonObject.put("explain", wordPrefixJSONArray);
							wordRootJsonObject.put("explain", wordRootJSONArray);
							wordSuffixJsonObject.put("explain", wordSuffixJSONArray);
							jsonObject.put("wordPrefix", wordPrefixJsonObject);
							jsonObject.put("wordRoot", wordRootJsonObject);
							jsonObject.put("wordSuffix", wordSuffixJsonObject);

						}

						break;
					}
				}
			}
			//
		}

		System.out.println(jsonObject.toString());
		return jsonObject;
	}

	@SuppressWarnings("unused")
	public static void parserHtmlSecontType(String fileName) {
		JSONObject result = new JSONObject();
		result.put("state", "ok");
		File file = new File(fileName);
		if (!file.exists() || !file.isFile() || file.length() <= 0) {
			return;
		}
		Source source = null;
		try {
			Config.LoggerProvider = LoggerProvider.DISABLED;
			source = new Source(file);
			if (source == null) {
				return;
			}
		} catch (IOException e) {
			e.printStackTrace();
			return;
		}

		List<StartTag> tagList = source.getAllStartTagsByClass("error-note");
		if (null != tagList && tagList.size() > 0) {
			Element el = tagList.get(0).getElement().getChildElements().get(0).getChildElements().get(0);
			String message = el.getContent().toString();
			return;
		}
		List<Element> infoArticleElementList = source.getAllElementsByClass("info-article");

		List<WordPreRootSufix> list = new ArrayList<WordPreRootSufix>();

		for (int i = 0; i < infoArticleElementList.size(); i++) {
			List<Element> tArticleList = infoArticleElementList.get(i).getAllElementsByClass("article-list");
			if (tArticleList != null && tArticleList.size() == 1) {
				List<Element> tCurrentList = tArticleList.get(0).getAllElementsByClass("current");
				if (tCurrentList != null && tCurrentList.size() == 1) {

					String wordRootText = tCurrentList.get(0).getContent().toString();
					if (wordRootText != null && wordRootText.equals("词根词缀")) {
						System.out.println("zhao-dao-le");

						String etymaAffix = ""; // 词根词缀
						String type = "0"; // 类型 1-> 前缀， 2-> 词根 3-> 后缀

						List<Element> articleList = infoArticleElementList.get(i).getAllElementsByClass("article");
						if (articleList != null && articleList.size() == 1) {
							Element articleElement = articleList.get(0);
							List<Element> elementList = articleElement.getChildElements();
							String wordPrefix = "";
							String wordRoot = "";
							String wordSuffix = "";
							String flag = "1";
							// 找到词根词缀，开始解析
							for (int j = 0; j < elementList.size(); j++) {
								Element element = elementList.get(j);
								element.getContent().toString();
								String className = element.getAttributeValue("class");

								// 词根词缀名处理
								if (className.contains("word-root")) {
									//
									String tmpStr = element.getContent().toString();
									if (tmpStr.startsWith("前缀")) { // 前缀
										wordPrefix = tmpStr;
										flag = "1";
									} else if (tmpStr.startsWith("词根")) { // 词根
										wordRoot = tmpStr;
										flag = "2";
									} else if (tmpStr.startsWith("后缀")) { // 后缀
										wordSuffix = tmpStr;
										flag = "3";
									}

									etymaAffix = tmpStr;
//									type = Integer.parseInt(flag);
									type = flag;

								} else if (className.contains("collins-section")) { // 词根词缀内容处理
									if (flag.equals("1")) {
										JSONObject wordClassJsonObject = new JSONObject();

										List<Element> collinsSectionList = element.getChildElements();
										if (collinsSectionList != null && collinsSectionList.size() > 0) {
											JSONArray wordDetailJSONArray = new JSONArray();
											String hOrder = "";
											String wordClass = "";
											for (int k = 0; k < collinsSectionList.size(); k++) {
												Element subElement = collinsSectionList.get(k);
												String subClassName = subElement.getAttributeValue("class");

												if (subClassName.contains("section-h")) { // section-h
													List<Element> sectionHList = subElement
															.getAllElementsByClass("family-english");
													hOrder = sectionHList.get(0).getContent().toString();
													wordClass = sectionHList.get(1).getContent().toString();
													wordClassJsonObject.put("wordClass", hOrder + ":" + wordClass);
												} else if (subClassName.contains("section-prep")) { // section-prep
													// prep-order-icon
													String prepOrderIcon = subElement
															.getAllElementsByClass("prep-order-icon").get(0)
															.getContent().toString();
													// prep-order
													Element prepOrderElement = subElement.getAllElementsByClass(
															"prep-order").get(0);
													List<Element> subPreOrderList = prepOrderElement.getChildElements();
													String word = subPreOrderList.get(0).getTextExtractor().toString();
													String explaination = subPreOrderList.get(1).getTextExtractor()
															.toString();

													WordPreRootSufix wordPreRootSufix = new WordPreRootSufix();
													wordPreRootSufix.setPartWord(etymaAffix);
													wordPreRootSufix.setType(type);
													wordPreRootSufix.setWordClass(wordClass);
													//wordPreRootSufix.setWord(word.split(" ")[0]);
													wordPreRootSufix.setWordTranslation(word.substring(
															word.indexOf(" ")).trim());
													wordPreRootSufix.setExplaination(explaination);
													list.add(wordPreRootSufix);

												}
											}
										}

									} else if (flag.equals("2")) {
										JSONObject wordClassJsonObject = new JSONObject();

										List<Element> collinsSectionList = element.getChildElements();
										if (collinsSectionList != null && collinsSectionList.size() > 0) {
											JSONArray wordDetailJSONArray = new JSONArray();
											String hOrder = "";
											String wordClass = "";
											for (int k = 0; k < collinsSectionList.size(); k++) {
												Element subElement = collinsSectionList.get(k);
												String subClassName = subElement.getAttributeValue("class");

												if (subClassName.contains("section-h")) { // section-h
													List<Element> sectionHList = subElement
															.getAllElementsByClass("family-english");
													hOrder = sectionHList.get(0).getContent().toString();
													wordClass = sectionHList.get(1).getContent().toString();
													wordClassJsonObject.put("wordClass", hOrder + ":" + wordClass);
												} else if (subClassName.contains("section-prep")) { // section-prep
													// prep-order-icon
													String prepOrderIcon = subElement
															.getAllElementsByClass("prep-order-icon").get(0)
															.getContent().toString();
													// prep-order
													Element prepOrderElement = subElement.getAllElementsByClass(
															"prep-order").get(0);
													List<Element> subPreOrderList = prepOrderElement.getChildElements();
													String word = subPreOrderList.get(0).getTextExtractor().toString();
													String explaination = subPreOrderList.get(1).getTextExtractor()
															.toString();

													WordPreRootSufix wordPreRootSufix = new WordPreRootSufix();
													wordPreRootSufix.setPartWord(etymaAffix);
													wordPreRootSufix.setType(type);
													wordPreRootSufix.setWordClass(wordClass);
													//wordPreRootSufix.setWord(word.split(" ")[0]);
													wordPreRootSufix.setWordTranslation(word.substring(
															word.indexOf(" ")).trim());
													wordPreRootSufix.setExplaination(explaination);
													list.add(wordPreRootSufix);

												}
											}
										}

									} else if (flag.equals("3")) { // 后缀
										JSONObject wordClassJsonObject = new JSONObject();

										List<Element> collinsSectionList = element.getChildElements();
										if (collinsSectionList != null && collinsSectionList.size() > 0) {
											JSONArray wordDetailJSONArray = new JSONArray();
											String hOrder = "";
											String wordClass = "";
											for (int k = 0; k < collinsSectionList.size(); k++) {
												Element subElement = collinsSectionList.get(k);
												String subClassName = subElement.getAttributeValue("class");

												if (subClassName.contains("section-h")) { // section-h
													List<Element> sectionHList = subElement
															.getAllElementsByClass("family-english");
													hOrder = sectionHList.get(0).getContent().toString();
													wordClass = sectionHList.get(1).getContent().toString();
													wordClassJsonObject.put("wordClass", hOrder + ":" + wordClass);
												} else if (subClassName.contains("section-prep")) { // section-prep
													// prep-order-icon
													String prepOrderIcon = subElement
															.getAllElementsByClass("prep-order-icon").get(0)
															.getContent().toString();
													// prep-order
													Element prepOrderElement = subElement.getAllElementsByClass(
															"prep-order").get(0);
													List<Element> subPreOrderList = prepOrderElement.getChildElements();
													String word = subPreOrderList.get(0).getTextExtractor().toString();
													String explaination = subPreOrderList.get(1).getTextExtractor()
															.toString();

													WordPreRootSufix wordPreRootSufix = new WordPreRootSufix();
													wordPreRootSufix.setPartWord(etymaAffix);
													wordPreRootSufix.setType(type);
													wordPreRootSufix.setWordClass(wordClass);
													//wordPreRootSufix.setWord(word.split(" ")[0]);
													wordPreRootSufix.setWordTranslation(word.substring(
															word.indexOf(" ")).trim());
													wordPreRootSufix.setExplaination(explaination);
													list.add(wordPreRootSufix);
												}
											}
										}

									} else {
										// 不是有效数据
										System.err.println("---err");
									}

								}

							}

						}

						break;
					}
				}
			}
			
		}

		for (int n = 0; n < list.size(); n++) {
			System.out.println(list.get(n));
		}
		System.out.println("总大小：" + list.size());
		try {
			DictionaryUtils.updateWordPreRootSufix(list);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return;
	}

	public static void exportWordPreRootSufix(String filePath, List<WordPreRootSufix> list) {
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFRow row = null;
			XSSFCell cell = null;

			sheet.setColumnWidth(0, 50*256);
			sheet.setColumnWidth(1, 10*256);
			sheet.setColumnWidth(2, 15*256);
			sheet.setColumnWidth(3, 15*256);
			sheet.setColumnWidth(4, 30*256);
			sheet.setColumnWidth(5, 80*256);
			sheet.setColumnWidth(6, 15*256);
			// 循环行
			String lastCigen = "";
			Short colorIndex= IndexedColors.AUTOMATIC.getIndex();;
//			for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
			for (int i = 0; i < list.size(); i++) {
//			for (int i = 0; i < 100; i++) {
				row = sheet.getRow(i);
				if (row == null) {// 判断是否为空
					row = sheet.createRow(i);
					// continue;
				}
				WordPreRootSufix wordPreRootSufix = list.get(i);
				
				for (int j = 0; j < 6; j++) {
					//设置格式
					XSSFCell cell0 = row.createCell(0);// 创建单元格
					XSSFCell cell1 = row.createCell(1);// 创建单元格
					XSSFCell cell2 = row.createCell(2);// 创建单元格
					XSSFCell cell3 = row.createCell(3);// 创建单元格
					XSSFCell cell4 = row.createCell(4);// 创建单元格
					XSSFCell cell5 = row.createCell(5);// 创建单元格
					XSSFCell cell6 = row.createCell(6);// 创建单元格
					CellStyle style = wb.createCellStyle();
					
					if(lastCigen.equalsIgnoreCase(wordPreRootSufix.getPartWord())){
//						style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
					}else{
//						style.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
						if(colorIndex == IndexedColors.AUTOMATIC.getIndex()){
							colorIndex = IndexedColors.GREY_25_PERCENT.getIndex();
						}else{
							colorIndex = IndexedColors.AUTOMATIC.getIndex();
						}
					}
					style.setFillForegroundColor(colorIndex);
					
//					style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
					style.setFillPattern(CellStyle.SOLID_FOREGROUND);
					lastCigen = wordPreRootSufix.getPartWord();
					
					style.setWrapText(true);
					cell0.setCellStyle(style);
					cell1.setCellStyle(style);
					cell2.setCellStyle(style);
					cell3.setCellStyle(style);
					cell4.setCellStyle(style);
					cell5.setCellStyle(style);
					cell6.setCellStyle(style);
					
					
					
					cell0.setCellValue(wordPreRootSufix.getPartWord());
					cell1.setCellValue(wordPreRootSufix.getType());
					cell2.setCellValue(wordPreRootSufix.getWordClass());
					//cell3.setCellValue(wordPreRootSufix.getWord());
					cell4.setCellValue(wordPreRootSufix.getWordTranslation());
					cell5.setCellValue(wordPreRootSufix.getExplaination());
					cell6.setCellValue(wordPreRootSufix.getSeqNo());
				}

			}

			FileOutputStream out = new FileOutputStream(filePath);
			wb.write(out);
			out.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	
	
	public static void importEtymaAffix(String filePath) {
		try {
			XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
			List<WordPreRootSufix> list = new ArrayList<WordPreRootSufix>();

			XSSFSheet sheet = wb.getSheetAt(0);
			XSSFRow row = null;
			XSSFCell cell = null;

			for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
				// for (int i = 0; i < 100; i++) {
				row = sheet.getRow(i);
				if (row.getCell(0) == null) {
					break;
				}
				System.out.println("--i->" + i);
				String cell0 = "";
				if (row.getCell((short) 0).getCellType() == XSSFCell.CELL_TYPE_STRING) {
					cell0 = String.valueOf(row.getCell((short) 0).getStringCellValue());
				} else {
					cell0 = String.valueOf(row.getCell((short) 0).getRawValue());
				}
				if (StringUtils.isEmpty(cell0) || cell0.equals("null")) {
					break;
				}
				System.out.println(cell0);

				WordPreRootSufix wordPreRootSufix = new WordPreRootSufix();
//				wordPreRootSufix.setPartWord(String.valueOf(row.getCell((short) 0)));
//				wordPreRootSufix.setType(Integer.parseInt(String.valueOf(row.getCell((short) 1).getRawValue())));
//				wordPreRootSufix.setWordClass(String.valueOf(row.getCell((short) 2)));
//				wordPreRootSufix.setWord(String.valueOf(row.getCell((short) 3)));
//				wordPreRootSufix.setSeqNo(Integer.parseInt(String.valueOf(row.getCell((short) 4).getRawValue())));
//				wordPreRootSufix.setWordTranslation(String.valueOf(row.getCell((short) 5)));
//				wordPreRootSufix.setExplaination(String.valueOf(row.getCell((short) 6)));

				wordPreRootSufix.setPartWord(String.valueOf(row.getCell((short) 0)));
				wordPreRootSufix.setType(String.valueOf(row.getCell((short) 1)));
				wordPreRootSufix.setWordClass(String.valueOf(row.getCell((short) 2)));
				//wordPreRootSufix.setWord(String.valueOf(row.getCell((short) 3)));
				wordPreRootSufix.setSeqNo(Integer.parseInt(String.valueOf(row.getCell((short) 6).getRawValue())));
				wordPreRootSufix.setWordTranslation(String.valueOf(row.getCell((short) 4)));
				wordPreRootSufix.setExplaination(String.valueOf(row.getCell((short) 5)));
				list.add(wordPreRootSufix);

			}

			DictionaryUtils.updateWordPreRootSufix(list);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	
	
	public static void fixWordPreRootSufix() throws ClassNotFoundException, SQLException{
		List<WordPreRootSufix> list = DictionaryUtils.getWordPreRootSufix(0, Integer.MAX_VALUE);

		for (WordPreRootSufix wordPreRootSufix : list) {
//			String str = "我爱你，！？（）：；“”、。";
			String str  = wordPreRootSufix.getPartWord();

			char[] charArray = str.toCharArray();
			boolean flag = false;
			int index = 0;
			for (int i = 2; i < charArray.length; i++) {
				flag = isChinese(charArray[i]);
				if (flag) {
					index = i;
					break;
				}
			}
			wordPreRootSufix.setRootAffixLetter(str.substring(0, index).substring(3).replace("-", "").trim());
			// 生成json数组
			String rootAffixTranslation = str.substring(index).trim();
			JSONArray jsonArray = new JSONArray();
			if(rootAffixTranslation.startsWith("1")){
				for (int j = 2; j <= 6; j++) {
					if(rootAffixTranslation.contains(j+".")){
						jsonArray.add(rootAffixTranslation.substring(0, rootAffixTranslation.indexOf(j+".")).trim());
						rootAffixTranslation = rootAffixTranslation.substring(rootAffixTranslation.indexOf(j+".")).trim();
					}else{
						jsonArray.add(rootAffixTranslation);
						break;
					}

				}

			}else{
				jsonArray.add(rootAffixTranslation);
			}
			
			if (jsonArray.size() < wordPreRootSufix.getSeqNo()) {
				//System.err.println(wordPreRootSufix.getPartWord() + "--" + wordPreRootSufix.getWord() + "--seqno 错误--"+wordPreRootSufix.getSeqNo());
			}
//			wordPreRootSufix.setRootAffixTranslation(str.substring(index).trim());
			wordPreRootSufix.setRootAffixTranslation(jsonArray.toString());
//			System.out.println("==" + str.substring(0, index).trim() + "--------------" + str.substring(index).trim());
		}
		DictionaryUtils.updateWordPreRootSufix(list);
	}
	
	
	public static boolean isChinese(char c) {
		Character.UnicodeBlock ub = Character.UnicodeBlock.of(c);
		if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS) {
			System.out.println(c + "--CJK_UNIFIED_IDEOGRAPHS");
			return true;
		} else if (ub == Character.UnicodeBlock.CJK_COMPATIBILITY_IDEOGRAPHS) {
			System.out.println(c + "--CJK_COMPATIBILITY_IDEOGRAPHS");
		} else if (ub == Character.UnicodeBlock.CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A) {
			// CJK Unified Ideographs Extension WikipediaUnicode扩展汉字
			// CJK Unified Ideographs Extension A 中日韩统一表意文字扩展区A ; 表意文字扩充A
			// CJK Unified Ideographs Extension B 中日韩统一表意文字扩展区B
			System.out.println(c + "--CJK_UNIFIED_IDEOGRAPHS_EXTENSION_A");
		} else if (ub == Character.UnicodeBlock.GENERAL_PUNCTUATION) {// 通用标点
			System.out.println(c + "--GENERAL_PUNCTUATION");

		} else if (ub == Character.UnicodeBlock.CJK_SYMBOLS_AND_PUNCTUATION) {
			System.out.println(c + "--CJK_SYMBOLS_AND_PUNCTUATION");

		} else if (ub == Character.UnicodeBlock.HALFWIDTH_AND_FULLWIDTH_FORMS) {
			System.out.println(c + "--HALFWIDTH_AND_FULLWIDTH_FORMS");

		}
		
		if(c=='='){
			return true;
		}else if(c=='1'){
			return true;
		}
		return false;
		
	}
	 
	public static String filterSpecialChar(String content) {
		content = content.replaceAll("(?<=>)\\s+(?=<)", "");
		content = content.replaceAll("<.+?>", "");
		content = content.replaceAll("[\f,\n,\r,\t]", "");
		content = content.replaceAll(" {2,}", " ");
		content = content.replaceAll("&[a-zA-Z]{1,10};", " ");
		return content.trim();
	}

	public static JSONObject _transEE(Element element) {
		JSONObject EE = new JSONObject();
		if (null == element || element.isEmpty()) {
			return null;
		}
		Element ul = element.getFirstElement("div").getFirstElement("ul");
		JSONArray trans = getUlText(ul);
		EE.put("trans", trans);
		return EE;
	}

	public static JSONArray getUlText(Element el) {
		JSONArray UL = new JSONArray();
		if (null != el && !el.isEmpty()) {
			List<Element> li = el.getChildElements();
			if (null != li && !li.isEmpty()) {
				for (int i = 0; i < li.size(); i++) {
					if (null == li.get(i) || li.get(i).isEmpty()) {
						continue;
					}
					List<Element> children = li.get(i).getChildElements();
					JSONObject tranSub = new JSONObject();
					JSONArray tranSubA = new JSONArray();
					String key = null;
					JSONArray value = new JSONArray();
					for (int j = 0; j < children.size(); j++) {
						Element child = children.get(j);
						if (null == child || child.isEmpty()) {
							continue;
						}
						String type = child.getName();
						if (type.equals("span")) {
							key = filterSpecialChar(child.getContent().toString());
						} else if (type.equals("ul")) {
							value = getUlText(child);
						} else if (type.equals("p")) {
							List<Element> pChildren = child.getChildElements();
							if (null != pChildren && pChildren.size() > 0) {
								for (int k = 0; k < pChildren.size(); k++) {
									value.add(filterSpecialChar(pChildren.get(k).getContent().toString()));
								}
							} else {
								value.add(filterSpecialChar(child.getContent().toString()));
							}
						} else if (type.equals("div")) {
							Element dspan = child.getFirstElement("span");
							List<Element> dp = child.getAllElements("p");
							if (null != dspan && !dspan.isEmpty()) {
								key = filterSpecialChar(dspan.getContent().toString());
							}
							if (null != dp && !dp.isEmpty()) {
								for (int k = 0; k < dp.size(); k++) {
									value.add(filterSpecialChar(dp.get(k).getContent().toString()));
								}
							}
						}
					}
					if (null == key) {
						key = getText(li.get(i));
						if (null == key || "".equals(key)) {
							continue;
						}
					}
					if (null != value && value.size() > 0) {
						tranSub.put(key, value);
						UL.add(tranSub);
					} else {
						UL.add(key);
					}

				}
			}
		}
		if (UL.isEmpty()) {
			return null;
		}
		return UL;
	}

	public static String getText(Element el) {
		String text = null;
		if (null != el && !el.isEmpty()) {
			text = el.toString();
			String key = "<.+?>";
			Pattern p = Pattern.compile(key);
			Matcher m = p.matcher(text);
			int count = 0, startTag = 0, start = 0;
			String result = "";
			while (m.find()) {
				String tag = m.group(0);
				if (start != 0) {
					result += text.substring(start, text.indexOf(tag, count));
					start = 0;
				}
				if (tag.startsWith("</")) {
					startTag--;
					if (startTag == 1) {
						start = text.indexOf(tag, count) + tag.length();
					} else if (startTag == 0) {
						result += text.substring(count, text.indexOf(tag, count));
					}
				} else {
					startTag++;
				}
				count = text.indexOf(tag, count) + tag.length();
			}
			result = result.replaceAll("<[^/]+?>[\\s,\\S]*</.+?>", "");
			result = result.replaceAll("[\f,\n,\r,\t]", "");
			result = result.replaceAll(" {2,}", " ");
			result = result.replaceAll("&[a-zA-Z]{1,10};", " ");
			result = result.trim();
			text = result;
		}
		return text;
	}

}
